﻿SET NOCOUNT ON
GO
USE [VNPT_Bsc]
GO
if(object_id('GetAll','P')is not null)
	drop proc GetAll
go
create proc [dbo].[GetAll]
	@tableName nvarchar(128),
	@orderBy nvarchar(512)
AS
	declare @sql nvarchar(1000)
	set @sql='select * from '+@tableName+' '+@orderBy+''
	exec (@sql)
GO
if exists (select * from dbo.sysobjects where id = object_id(N'InsertUpdateDelete_fact_costs_revenues') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure InsertUpdateDelete_fact_costs_revenues
GO
CREATE PROCEDURE InsertUpdateDelete_fact_costs_revenues

	@branch_code varchar(10) = null ,
	@cost_code varchar(50) = null ,
	@timekey int = null ,
	@cost float = null ,
	@unit_code varchar(10) = null ,
	@Action int
AS

if @Action=0
	begin
		insert into fact_costs_revenues values( @branch_code, @cost_code,@unit_code, @timekey, @cost)
	end
else if @Action=1
	begin
		update fact_costs_revenues set cost_code=@cost_code,timekey=@timekey,cost=@cost where branch_code=@branch_code and cost_code=@cost_code and unit_code=@unit_code and timekey=@timekey
	end
else if @Action=2
	begin
		delete fact_costs_revenues where branch_code=@branch_code
	end
RETURN
GO

if exists (select * from dbo.sysobjects where id = object_id(N'InsertUpdateDelete_dim_unit') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure InsertUpdateDelete_dim_unit
GO
CREATE PROCEDURE InsertUpdateDelete_dim_unit

	@code varchar(10) ,
	@name nvarchar(100) = null ,
	@slug varchar(200) ,
	@Action int
AS

if @Action=0
	begin
		insert into dim_unit values( @code, @name, @slug)
	end
else if @Action=1
	begin
		update dim_unit set name=@name,slug=@slug where code=@code
	end
else if @Action=2
	begin
		delete dim_unit where code=@code
	end
RETURN
GO

if exists (select * from dbo.sysobjects where id = object_id(N'InsertUpdateDelete_fact_costs_revenues_target') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure InsertUpdateDelete_fact_costs_revenues_target
GO
CREATE PROCEDURE InsertUpdateDelete_fact_costs_revenues_target

	@cost_code varchar(10) ,
	@unit nchar(10) = null ,
	@timekey int = null ,
	@target float = null ,
	@branch_code varchar(10) = null ,
	@Action int
AS

if @Action=0
	begin
		insert into fact_costs_revenues_target values( @cost_code, @unit, @timekey, @target, @branch_code)
	end
else if @Action=1
	begin
		update fact_costs_revenues_target set unit=@unit,timekey=@timekey,target=@target,branch_code=@branch_code where cost_code=@cost_code
	end
else if @Action=2
	begin
		delete fact_costs_revenues_target where cost_code=@cost_code
	end
RETURN
GO

if exists (select * from dbo.sysobjects where id = object_id(N'InsertUpdateDelete_dim_costs_revenues') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure InsertUpdateDelete_dim_costs_revenues
GO
CREATE PROCEDURE InsertUpdateDelete_dim_costs_revenues

	@code varchar(50) ,
	@name nvarchar(200) = null ,
	@unit_code varchar(10) ,
	@code2 nvarchar(100) = null ,
	@code3 nvarchar(100) = null ,
	@code4 nchar(10) = null ,
	@code5 nchar(10) = null ,
	@parent_code varchar(50) = null ,
	@note nvarchar(1000) = null ,
	@slug varchar(500) = null ,
	@Action int
AS

if @Action=0
	begin
		insert into dim_costs_revenues values( @code, @name, @unit_code, @code2, @code3, @code4, @code5, @parent_code, @note, @slug)
	end
else if @Action=1
	begin
		update dim_costs_revenues set name=@name,unit_code=@unit_code,code2=@code2,code3=@code3,code4=@code4,code5=@code5,parent_code=@parent_code,note=@note,slug=@slug where code=@code
	end
else if @Action=2
	begin
		delete dim_costs_revenues where code=@code
	end
RETURN
GO
---------------------------------------------------------------------
if OBJECT_ID('spGetCostByKey','P') is not null
	drop proc spGetCostByKey
go
create proc spGetCostByKey @branch_code varchar(10)=null,@cost_code varchar(50)=null,@timekey int=null,@unit_code varchar(10)=null
as
	DECLARE  @query varchar(max)
	DECLARE  @where varchar(max)
	set @query='SELECT * FROM dbo.fact_costs_revenues'
	set @where=''
	if(@branch_code is not null)
	begin
		if(@where<>'') set @where= @where + ' AND '
		set @where=@where+'branch_code='''+@branch_code+''''
	end
	if(@cost_code is not null)
	begin
		if(@where<>'') set @where= @where + ' AND '
		set @where=@where+'cost_code='''+@cost_code+''''
	end
	if(@timekey is not null)
	begin
		if(@where<>'') set @where= @where + ' AND '
		set @where=@where+'timekey='+cast(@timekey as varchar(10))
	end
	if(@unit_code is not null)
	begin
		if(@where<>'') set @where= @where + ' AND '
		set @where=@where+'unit_code='''+@unit_code+''''
	end
	if(@where<>'')
		set @query=@query+' where '+@where	
	--print @query
	exec (@query)
---------------------------------------------------------------------------------
if OBJECT_ID('[spGetFactCost]','P') is not null
        drop proc [spGetFactCost]
go
create  proc [dbo].[spGetFactCost] @branch_code varchar(10)=null,@cost_code varchar(50)=null,
	@timekey int=null,@unit_code varchar(10)=null,@from int=null,@to int=null
as
        DECLARE  @query Nvarchar(max)
        DECLARE  @where Nvarchar(max)
        set @query=N'select ISNULL(f.cost,0) as cost,f.timekey,right(f.timekey,2)+''/''+left(f.timekey,4) as [month],b.code as branch_code'
        set @query=@query+N',b.name as branch_name,d.code as cost_code,d.name as criteria_name'
        set @query=@query+N',u.code as unit_code,u.name as unit_name,'
        --set @query=@query+N',u.code as unit_code,u.name as unit_name,'
        set @query=@query+N'ISNULL((select SUM(cost) from dbo.fact_costs_revenues f1 where f1.cost_code=f.cost_code and f1.branch_code=f.branch_code and f1.timekey<=f.timekey),0) as accumulate'
        set @query=@query+N' ,case left(f.cost_code,1) when ''C'' then ''Chi phí'' when ''R'' then ''Doanh thu'' end as [type]'
        set @query=@query+N' from dbo.fact_costs_revenues f right join  dbo.dim_costs_revenues d on (f.cost_code=d.code and f.unit_code=d.unit_code)'
        set @query=@query+N' right join dbo.dim_unit u on (d.unit_code=u.code) right join VNPT_common.dbo.branch b on f.branch_code=b.code'
        set @where=''
        --set @where=N'd.name <> N''Trong đó: thu ngoại tệ (USD)'''
        if(@branch_code is not null)
        begin
                if(@where<>'') set @where= @where + ' AND '
                set @where=@where+N'branch_code=N'''+@branch_code+''''
        end
        if(@cost_code is not null)
        begin
                if(@where<>'') set @where= @where + ' AND '
                set @where=@where+N'cost_code=N'''+@cost_code+''''
        end
        if(@timekey is not null)
        begin
                if(@where<>'') set @where= @where + ' AND '
                set @where=@where+'timekey='+cast(@timekey as varchar(10))
        end
        if(@unit_code is not null)
        begin
                if(@where<>'') set @where= @where + ' AND '
                set @where=@where+N'unit_code=N'''+@unit_code+''''
        end
        if(@from is not null)
        begin
			--print 'aaaaa'
                if(@where<>'')set @where= @where + ' AND '
                set @where=@where+N'f.timekey>='+cast(@from as nvarchar(6))
            --print @where
        end
        if(@to is not null)
        begin
                if(@where<>'') set @where= @where + ' AND '
                set @where=@where+N'f.timekey<='+cast(@to as nvarchar(6))
        end
        if(@where<>'')
                set @query=@query+N' where '+@where + ' order by timekey asc, cost_code asc, b.code asc'
        --print @query
        --print @from
        exec (@query)
        go
        exec dbo.[spGetFactCost] null,null,null,null,201407,201411